package Q;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class EX01_AccountDAO implements EX01_IAccountDAO{
	
	EX01_AccountVO avo = new EX01_AccountVO();
	
	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:dlwntk6545/dlwntk6545@112.216.253.125:1251:xe";
	private String insertSQL = "insert into EX01ACCOUNT values(exam.nextval, ?, ?, ?, sysdate)";
	private String selectSQL = "select * from EX01Account order by ano desc";
	private String updateSQL = "update EX01Account set aid = ?, balance = ?, interestRate = ?, dateCreated = ?";
	private String deleteSQL = "delete EX01Account where ano = ?";
	
	@Override
	public boolean insertAccount(EX01_AccountVO avo) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn = this.openDatabase();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			this.closeDatabase(conn, pstmt, null);
		}
		return result;
	}

	@Override
	public List<EX01_AccountVO> selectAccount() {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<EX01_AccountVO> result = new ArrayList<>();
		
		try{
			conn = openDatabase();
			pstmt = conn.prepareStatement(selectSQL);
			pstmt.setString(1, avo.getAid());
			pstmt.setInt(2, avo.getBalance());
			pstmt.setFloat(3, avo.getInterestRate());
			pstmt.executeUpdate();
			rs = pstmt.executeQuery();
			result = null;
			while(rs.next()){
				EX01_AccountVO a = new EX01_AccountVO();
				a.setAno(rs.getInt("ano"));
				a.setAid(rs.getString("aid"));
				a.setBalance(rs.getInt("balance"));
				a.setInterestRate(rs.getFloat("interestRate"));
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn, pstmt, null);
		}
		return result;
		
	}

	@Override
	public boolean updateAccount(EX01_AccountVO avo) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean deleteAccount(EX01_AccountVO avo) {
		// TODO Auto-generated method stub
		return false;
	}
	
	
	private Connection openDatabase(){
		
		try{
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	
	private void closeDatabase(Connection c, PreparedStatement p, ResultSet r){
		
		if(c != null){
			try{c.close(); c = null;}
				catch(Exception e){}
			}
		if(p != null){
			try{p.close(); p = null;}
			catch(Exception e){}
		}
		if(r != null){
			try{r.close(); r = null;}
			catch(Exception e){}
		}
	}
		
}
	
	

